package com.zhenwgang.listen_books.repository;

import com.zhenwgang.listen_books.data_object.TrackDO;
import com.zhenwgang.listen_books.util.DBUtil;
import com.zhenwgang.listen_books.util.Log;
import com.zhenwgang.listen_books.view_object.ResultView;
import com.zhenwgang.listen_books.view_object.TrackContent;
import com.zhenwgang.listen_books.view_object.TrackView;
import lombok.SneakyThrows;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Violet
 * @date 2022/07/09 14:59
 **/
public class TrackRepo {
    @SneakyThrows
    public TrackContent selectContentByTid(int tid){
        try (Connection c = DBUtil.connection()) {
            String sql = "select type, content from tracks where tid = ?";
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setString(1, String.valueOf(tid));

                Log.println("执行 SQL: " + ps);
                try (ResultSet rs = ps.executeQuery()) {
                    if (!rs.next()) {
                        throw new RuntimeException("tid 对应的音频不存在，404");
                    }

                    return new TrackContent(rs.getString("type"), rs.getBinaryStream("content"));
                }
            }
        }
    }

    @SneakyThrows
    public void selectListByUidAndTidNotIn(int uid, int aid, ResultView resultView){
        try (Connection c = DBUtil.connection()) {
            String sql = "select tid, title from tracks where uid = ? and tid not in (select tid from relations where aid = ?) order by tid desc";
            try (PreparedStatement ps = c.prepareStatement(sql)) {
                ps.setInt(1, uid);
                ps.setString(2, String.valueOf(aid));

                try (ResultSet rs = ps.executeQuery()) {
                    while (rs.next()) {
                        int tid = rs.getInt("tid");
                        String title = rs.getString("title");
                        resultView.trackList.add(new TrackView(tid, title));
                    }
                }
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByTid(List<Integer> tidList){
        String format = "select tid, uid, title, type from tracks where tid in (%s)";
        StringBuilder sb = new StringBuilder();
        for (int tid : tidList){
            sb.append(tid).append(", ");
        }
        if (sb.length() > 0) {
            sb.delete(sb.length() - 2, sb.length());
        }
        String sql = String.format(format, sb);
        List<TrackDO> list = new ArrayList<>();
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                Log.println(ps);

                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        TrackDO trackDO = new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        );
                        list.add(trackDO);
                    }
                }
            }
        }
        return list;
    }

    @SneakyThrows
    public void insert(Integer uid, String title, String type, InputStream contentInputStream) {
        String sql = "insert into tracks (uid, title, type, content) values(?, ?, ?, ?)";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, uid);
                ps.setString(2, title);
                ps.setString(3, type);
                ps.setBinaryStream(4, contentInputStream);

                Log.println("执行ps：" + ps.toString());
                ps.executeUpdate();
            }
        }
    }

    @SneakyThrows
    public List<TrackDO> selectListByUidLimitOffset(int uid, int limit, int offset){
        List<TrackDO> list = new ArrayList<>();
        String sql = "select tid, uid, title, type from tracks where uid = ? order by tid desc limit ? offset ? ";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, uid);
                ps.setInt(2, limit);
                ps.setInt(3, offset);

                Log.println("执行的SQL为：" + sql);
                try (ResultSet rs = ps.executeQuery()){
                    while (rs.next()){
                        TrackDO trackDO = new TrackDO(
                                rs.getInt("tid"),
                                rs.getInt("uid"),
                                rs.getString("title"),
                                rs.getString("type")
                        );
                        list.add(trackDO);
                    }
                }
            }
        }
        return list;
    }

    @SneakyThrows
    public int selectCountByUid(int uid){
        String sql = "select count(*) from tracks where uid = ?";
        try (Connection c = DBUtil.connection()){
            try (PreparedStatement ps = c.prepareStatement(sql)){
                ps.setInt(1, uid);
                Log.println("执行的SQL为：" + sql);

                try (ResultSet rs = ps.executeQuery()){
                    rs.next();
                    return rs.getInt(1);
                }
            }
        }
    }
}